package com.academic_system.mapper;

import com.academic_system.model.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Description:党建管理 学生申请和老师处理dao层
 */
@Repository
public interface PartyApplyDao {
    /**
     * @param: StuApply stuApply
     * @Description ：学生申请入党,插入一条学生申请入党记录
     * @methodName ：addApplyParty
     * @returnType ：void
     **/

    @Insert("insert into stu_apply(stu_info_id,stu_num,stu_name,apply_date,party_organization_num,apply_party_state) " +
            "values(#{stuInfoId},#{stuNum},#{stuName},#{applyDate},#{partyOrganizationNum},0)")
    int addApplyParty(StuApply stuApply);


    /**
     * @param
     * @Description: 老师同意学生入党，通过入党申请表主键id将state改为1
     * @methodName: replyOfStuApplyParty
     * @returnType: void
     **/

    @Update("update stu_apply set apply_party_state= 1 where stu_apply_id = #{stuApplyId}")
    int replyOfStuApplyPartyYes(@Param("stuApplyId") int stuApplyid);

    /**
     * @param:PartyMember partyMember
     * @Description :老师点击同意入党的同时往党员表添加一条新的对应记录
     * @methodName ：addPartyMember
     * @returnType: int
     **/

    @Insert("insert into party_member(party_member_id,party_info_id,te_info_id,stu_info_id,party_member_account," +
            "party_member_name,party_member_type,party_organization_num,apply_date)" +
            "values(NULL,#{partyInfoId},#{teInfoId},#{stuInfoId},#{partyMemberAccount},#{partyMemberName}," +
            "#{partyMemberType},#{partyOrganizationNum},#{applyDate})")
    int addPartyMember(PartyMember partyMember);

    /**
     * @param: int stuApplyid
     * @Description: 老师拒绝学生入党，通过入党申请表主键id将state改为2
     * @methodName： replyOfStuApplyPartyDeny
     * @returnType： int
     **/

    @Update("update stu_apply set apply_party_state=2 where stu_apply_id = #{stuApplyId}")
    int replyOfStuApplyPartyDeny(@Param("stuApplyId") int stuApplyid);

    /**
     * @Description: 学生查询个人党员信息，信息显示
     * @methodName: queryPartyMemberInforByParty_member_id
     * @param: int party_member_id
     * @returnType: PartyMember
     **/

    @Select("select * from party_member where party_member_id=#{party_member_id}")
    PartyMember queryPartyMemberInforByParty_member_id(@Param("party_member_id") int party_member_id);

    /**
     * @param
     * @Description： 学生党员根据party_member_id查询个人所在党支部基本信息
     * @methodName
     * @returnType
     **/
//select party.party_info_id,party.party_organization_num,party.party_name,party.party_found_time,party.party_description
    @Select("select party.* " +
            "FROM party,party_member" +
            " WHERE party.party_organization_num=party_member.party_organization_num " +
            "AND party_member.party_member_id=#{party_member_id}"
    )
    Party queryPartyInforByParty_member_id(@Param("party_member_id") int party_member_id);

    /**
     * @param: int party_member_account，在此学生党员账号就是学生学号stuNum
     * @Description： 学生党员根据party_member_account查询个人所在党支部基本信息
     * @methodName: queryPartyByPartyMemberAccount
     * @returnType: Party
     **/
    @Select("select party.* " +
            "FROM party,party_member" +
            " WHERE party.party_organization_num=party_member.party_organization_num " +
            "AND party_member.party_member_account=#{party_member_account}"
    )
    Party queryPartyByPartyMemberAccount(@Param("party_member_account") int party_member_account);

    /**
     * @param ：int stu_num，学生学号
     * @Description :通过学生学号查询学生ID
     * @methodName :queryStuIdByStuNum
     * @returnType ：int
     **/

    @Select("select stu_info_id from student where stu_num=#{stu_num}")
    Integer queryStuIdByStuNum(@Param("stu_num") int stu_num);

    /**
     * @param ：int partyNum
     * @Description :通过党组织编号查询党组织ID
     * @methodName :queryPartyIdByPartyNum
     * @returnType ：int
     **/

    @Select("select party_info_id from party where party_organization_num=#{partyNum}")
    int queryPartyIdByPartyNum(@Param("partyNum") int partyNum);

    /**
     * @param
     * @Description :查询所有党组织
     * @methodName
     * @returnType
     **/
    @Select("select * from party")
    List<Party> queryAllPartys();

    /**
     * @param :int stu_num
     * @Description :通过学生学号修改学生政治面貌信息
     * @methodName :updateStudentPoli
     * @returnType :boolean
     **/

    @Update("update student set stu_poli= 1 where stu_num=#{stu_num}")
    boolean updateStudentPoli(@Param("stu_num") int stu_num);

    /**
     * @param
     * @Description :老师查询所有学生的申请入党记录
     * @methodName ：queryAllStuApplys
     * @returnType ： List<StuApply>
     **/
    @Select("select * from stu_apply")
    List<StuApply> queryAllStuApplys();

    /**
     * @param :StuApply stuApply
     * @Description :多条件查询
     * @methodName :queryApplyByConditions
     * @returnType : List<StuApply>
     **/

    @SelectProvider(type = PartyApplySqlProvider.class, method = "query")
    List<StuApply> queryApplyByConditions(StuApply stuApply);

    /**
     * @param ：int stu_apply_id
     * @Description :通过学生申请入党id查询对应的StuApply对象
     * @methodName ：querystuApplyBystuApplyId
     * @returnType ：StuApply
     **/
    @Select("select * from stu_apply where stu_apply_id=#{stu_apply_id}")
    StuApply querystuApplyBystuApplyId(@Param("stu_apply_id") int stu_apply_id);

    /**
     * @Description: 学生通过党员账号即学生学号查询个人党员信息
     * @methodName: querypartyMemberByAccount
     * @param: int party_member_account
     * @returnType: PartyMember
     **/

    @Select("select * from party_member where party_member_account=#{party_member_account}")
    PartyMember querypartyMemberByAccount(@Param("party_member_account") int party_member_account);

    /**
     * @param stu_Info_Id
     * @Description 根据学生id查询一个4级成绩
     * @methodName queryCet4
     * @returnType int
     **/
    @Select("select cet_4_grade from cet_46 where stu_Info_Id=#{stu_Info_Id}")
    int queryCet4(@Param("stu_Info_Id") int stu_Info_Id);

    /**
     * @param :int stuNum
     * @Description :通过学生党员账号即学生学号查看对应党组织政治面貌的统计
     * @methodName :queryPoliTypeByParMemberNum
     * @returnType :List<Map>
     **/
    @Select("select  p.party_info_id, pm.party_member_type,p.party_name,\n" +
            "(select count(pp.party_member_id) from party_member as pp where pp.party_info_id = pm.party_info_id and pp.party_member_type = pm.party_member_type) as typesize\n" +
            "from party as p\n" +
            "join party_member as pm\n" +
            "on pm.party_info_id = p.party_info_id\n" +
            "where p.party_info_id = (select pmp.party_info_id from party_member as pmp where pmp.party_member_account=#{stuNum})\n" +
            "group by pm.party_member_type\n" +
            "order by pm.party_member_type")
    List<Map> queryPoliTypeByParMemberNum(@Param("stuNum") int stuNum);

}
//select count(party_member_id),pm.party_member_type from party_member pm,
//            party p where pm.party_info_id=p.party_info_id and  pm.party_member_account=20151013 group by pm.party_member_type
//-xcytzz---
/*select  p.party_info_id, pm.party_member_type,
        (select count(pp.party_member_id) from party_member as pp where pp.party_info_id = pm.party_info_id and pp.party_member_type = pm.party_member_type) as typesize
        from party as p
        join party_member as pm
        on pm.party_info_id = p.party_info_id
        where p.party_info_id = (select pmp.party_info_id from party_member as pmp where pmp.party_member_account = 20151013)
        group by pm.party_member_type, p.party_info_id
        order by p.party_info_id,pm.party_member_type*/


